Method for expanding and collapsing data cells in a spreadsheet report

ABSTRACT

Data cells in a spreadsheet report are collapsed and expanded without disrupting other spreadsheet data. A user may want to drill down on data corresponding to a parent member of the report to display more detailed information about the parent member. Likewise, a user may want to drill up the displayed information corresponding to the parent member to hide detailed information associated with the parent member. Before expanding or collapsing a report, the spreadsheet is scanned for information to determine whether the display of other data in the spreadsheet would be disrupted by the expansion/collapse. The information may include cross-join information associated with the parent member, the number of child members associated with the parent member, the dimension of the report created by the parent member and the associated child members, and other reports that may be linked to the report that includes the parent member.

BACKGROUND OF THE INVENTION

A spreadsheet is a table of values arranged in rows and columns. Eachvalue can have a predefined relationship to the other values. If onevalue is changed, other values may also need to be changed. Spreadsheetapplications are computer programs that allow for the electroniccreation and manipulation of spreadsheets. In a spreadsheet application,each value sits in a data cell. A user may define what type of data isin each cell and how different cells depend on each other. Therelationships between cells are called formulas, and the names of thecells are called labels. Data may be entered into the spreadsheet afterthe cells and the formulas for linking the cells together are defined.Selected values may be modified and other values may change accordingly.

Data may be extracted from one or more files and presented to aspreadsheet application in a specified format. The data may be formattedinto a report in rows and columns. A free-form report includes datacells that are each formula-based such that the individual cells may bemoved around the spreadsheet without affecting the functionality of thereport. A free-form report is not confined to pre-defined ranges. Extrarows and columns may be inserted into a free-form report to customizethe report according to user needs.

A parent cell in a free-form report may have a number of associateddescendent (or child) cells. For example, a cell labeled U.S.A. (theparent cell) may be associated with up to fifty child cells (one cellfor each state of the union). A user may expand the parent cell todisplay the associated child cells. A user may also collapse the parentcell such that the child cells are not displayed. The collapsed parentcell may be displayed in the report proximate associated sibling cells(e.g., Canada, China, France). The expansion and collapse of child cellsin a free-form report may disrupt other reports or text in thespreadsheet. Furthermore, the expansion of a parent cell to display theassociated child cells may extend occupied data cells in the spreadsheetbeyond the spreadsheet border since the number of spreadsheet cells islimited (e.g. 65536 rows and 256 columns).

SUMMARY OF THE INVENTION

The present invention is directed to the collapse and expansion of datacells in a spreadsheet report such that the display of data in otherareas of the spreadsheet is not disrupted. A report may be expanded bydisplaying any child members associated with a parent member in thereport. The child members may be displayed proximate the parent member.The expanded report may also be collapsed such that the child membersassociated with the parent member are not displayed in the report. Thedata integrity of the spreadsheet is preserved by scanning thespreadsheet for information associated with the data cells of thereport, and then expanding or collapsing the report based on theinformation.

A user may want to drill down on data corresponding to the parent memberto display more detailed information about the parent member. Likewise,a user may want to drill up the displayed information corresponding tothe parent member to hide detailed information associated with theparent member. Before expanding or collapsing a report, the spreadsheetis scanned for information to determine whether other reports or text inthe spreadsheet would be disrupted by the expand/collapse operation. Theinformation may include cross-join information associated with thereport, the number of child members associated with the parent member,the dimension of the report created by the parent member and theassociated child members, and other reports that may be linked to thereport that includes the parent member.

The parent member is expanded into a report including the associatedchild members. The parent member is expanded based on the informationobtained by scanning the spreadsheet such that the expansion does notdisrupt the display of data cells in other areas of the spreadsheetexternal to the report. If the spreadsheet area between the verticalborders of the report and the vertical borders of the spreadsheet do notinclude any occupied data cells, then the rows in the spreadsheet may bereplaced with rows that include the child members such that the childmembers appear in the expanded report without disrupting the integrityof data in the spreadsheet external to the report. If data cells in thespreadsheet area to the left or to the right of the report are occupiedwith values, but data cells in the spreadsheet area above or below thereport are not occupied with values, then the report may be expanded byadding blank ranges of data cells to the report. The child members maythen be written to the added data cells. If data cells in thespreadsheet area above or below the report are occupied with values,then the report may only be expanded if the display of the spreadsheetdata external to the report is not disrupted by the expansion.

The child members may be collapsed such that only the associated parentmember is displayed in the report. The child members are collapsed basedon the information obtained by scanning the spreadsheet such that thecollapse does not disrupt the display of data cells in other areas ofthe spreadsheet external to the report. If the data cells are empty inthe spreadsheet area to the left and to the right of the report, all ofthe rows in the report that contain the child members are removed fromdisplay in the report. Any occupied spreadsheet data cells that arelocated below the report may be moved upward to occupy any data cellsvacated by the removed child members. If the spreadsheet area betweenthe left border of the report and the left border of the spreadsheet andright border of the report and the right border of the spreadsheetcontains at least one occupied data cell but the spreadsheet areabetween the lower border of the report and the lower border of thespreadsheet does not contain any occupied data cells, the rowscontaining the child members are removed from the report range such thatother data in the spreadsheet area to the right and to the left of thereport is not disrupted. If the spreadsheet area between the lower (orupper) border of the report and the lower (or upper) border of thespreadsheet contains occupied data cells, the report may only becollapsed if the display of data in the spreadsheet would not bedisrupted. Any remaining members of the report are moved upward asnecessary.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates a computing device that may be used according to anexample embodiment of the present invention.

FIG. 2 illustrates a block diagram of a system for expanding andcollapsing data cells in a spreadsheet report, in accordance with thepresent invention.

FIG. 3 illustrates the expansion and collapse of data cells in aspreadsheet report, in accordance with the present invention.

FIG. 4 illustrates the expansion and collapse of data cells in across-joined report in a spreadsheet application, in accordance with thepresent invention.

FIG. 5 illustrates how a determination is made to expand or collapsedata cells in a spreadsheet report without disrupting other data in thespreadsheet, in accordance with the present invention.

FIG. 6 is an operational flow diagram illustrating a process forexpanding data cells in a spreadsheet report, in accordance with thepresent invention.

FIG. 7 is an operational flow diagram illustrating a process forcollapsing data cells in a spreadsheet report, in accordance with thepresent invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT

Data cells in a spreadsheet report are collapsed and expanded withoutdisrupting the display of other spreadsheet data external to the report.A user may want to drill down on data corresponding to a parent memberof the report to display more detailed information about the parentmember. The more detailed information is presented as child members thatinclude detailed information about the data of the parent member.Likewise, a user may want to drill up the displayed informationcorresponding to the parent member to hide detailed informationassociated with the parent member. The expanded report is collapsed suchthat the child members associated with the parent member are notdisplayed in the report. Before expanding or collapsing the report, thespreadsheet is scanned for information to determine whether spreadsheetdata external to the report would be disrupted by theexpansion/collapse. The information may include cross-join informationassociated with the parent member, the number of child membersassociated with the parent member, the dimension of the report createdby the parent member and the associated child members, and other reportsthat may be linked to the report that includes the parent member.

Illustrative Operating Environment

With reference to FIG. 1, one example system for implementing theinvention includes a computing device, such as computing device 100.Computing device 100 may be configured as a client, a server, a mobiledevice, or any other computing device that interacts with data in anetwork based collaboration system. In a very basic configuration,computing device 100 typically includes at least one processing unit 102and system memory 104. Depending on the exact configuration and type ofcomputing device, system memory 104 may be volatile (such as RAM),non-volatile (such as ROM, flash memory, etc.) or some combination ofthe two. System memory 104 typically includes an operating system 105,one or more applications 106, and may include program data 107. Areporting module 108, which is described in detail below, is implementedwithin applications 106.

Computing device 100 may have additional features or functionality. Forexample, computing device 100 may also include additional data storagedevices (removable and/or non-removable) such as, for example, magneticdisks, optical disks, or tape. Such additional storage is illustrated inFIG. 1 by removable storage 109 and non-removable storage 110. Computerstorage media may include volatile and nonvolatile, removable andnon-removable media implemented in any method or technology for storageof information, such as computer readable instructions, data structures,program modules, or other data. System memory 104, removable storage 109and non-removable storage 110 are all examples of computer storagemedia. Computer storage media includes, but is not limited to, RAM, ROM,EEPROM, flash memory or other memory technology, CD-ROM, digitalversatile disks (DVD) or other optical storage, magnetic cassettes,magnetic tape, magnetic disk storage or other magnetic storage devices,or any other medium which can be used to store the desired informationand which can be accessed by computing device 100. Any such computerstorage media may be part of device 100. Computing device 100 may alsohave input device(s) 112 such as keyboard, mouse, pen, voice inputdevice, touch input device, etc. Output device(s) 114 such as a display,speakers, printer, etc. may also be included.

Computing device 100 also contains communication connections 116 thatallow the device to communicate with other computing devices 118, suchas over a network. Networks include local area networks and wide areanetworks, as well as other large scale networks including, but notlimited to, intranets and extranets. Communication connection 116 is oneexample of communication media. Communication media may typically beembodied by computer readable instructions, data structures, programmodules, or other data in a modulated data signal, such as a carrierwave or other transport mechanism, and includes any information deliverymedia. The term “modulated data signal” means a signal that has one ormore of its characteristics set or changed in such a manner as to encodeinformation in the signal. By way of example, and not limitation,communication media includes wired media such as a wired network ordirect-wired connection, and wireless media such as acoustic, RF,infrared and other wireless media. The term computer readable media asused herein includes both storage media and communication media.

Expanding and Collapsing Data Cells in a Spreadsheet Report

FIG. 2 illustrates a block diagram of a system for expanding andcollapsing data cells in a spreadsheet report. The system includesspreadsheet application 200, reporting module 210, and data store 220.Spreadsheet application 200 may be provided at a client. Data store 220may be provided at a server. Reporting module 210 communicates with datastore 220 to provide data to spreadsheet application 200.

Data in data store 220 may be processed by Online Analytical Processing(OLAP). OLAP is a category of software tools that provides analysis ofdata stored in a database. OLAP tools enable users to analyze differentdimensions of multiple data. For example, OLAP provides time series andtrend analysis views. OLAP is often used in data mining. The chiefcomponent of OLAP is the OLAP server, which is provided between a clientand a database management system. The OLAP server understands how datais organized in the database and has special functions for analyzing thedata.

Reporting module 210 may be a program that extracts data from one ormore files and presents the information to spreadsheet application 200in a specified format. Reporting module 210 may format the data into areport in rows and columns. The present invention is directed to thecollapse and expansion of data cells in a free-form report usingreporting module 210. The processing performed by reporting module 210is described in detail below.

FIG. 3 illustrates the expansion and collapse of data cells in aspreadsheet report. The figure shows a collapsed report 300 and anexpanded report 350. Collapsed report 300 includes parent members 310and sibling member 320. A member may be one individual data cell, or agroup of related data cells. The group of related data cells may beincluded within one row, one column, or a data region. A data region mayinclude multiple data cells in adjacent rows and columns. The dataregion is expanded/collapsed as one entity. As shown in the figure,parent member 310 includes three data cells each occupied with a value(e.g., USA, 9, and 18).

A user may wish to expand parent member 310. In one embodiment, the usermay expand parent member 310 by double clicking or right-clicking amouse anywhere within parent member 310. As shown in expanded report350, expanding parent member 310 causes child members 360, 370, 380 tobe displayed in the report in rows below parent member 310. Siblingmember 320 is moved downward to make room for child members 360, 370,380. In one embodiment, the report is expanded such that the childmembers appear above the associated parent member. In anotherembodiment, the parent member may be a column of values such that whenthe report is expanded, new columns appear in the report proximate theparent member. The new columns may appear proximate the parent member.

Expanded report 350 may be collapsed to form collapsed report 300 bydouble clicking or right clicking a mouse on any child member 360, 370,380. Known processes of expanding and collapsing reports may disruptother data in the spreadsheet that is external to the report. Thepresent invention preserves the integrity of data in the spreadsheet byscanning the spreadsheet for information associated with the parent andchild members of the report. The report may then be expanded orcollapsed based on the information.

FIG. 4 illustrates the expansion and collapse of data cells in across-joined spreadsheet report. A collapsed report 400 and an expandedreport 450 display data from two dimensions that are used in conjunctionwith each other (i.e., a cross-joined report). For example, salesinformation related to both food and drink may be combined to form onecross-joined report. A user may expand parent member 410 to reveal childmembers 460, 470, 480. Likewise, child members 460, 470, 480 in expandedreport 450 may be collapsed to form collapsed report 400.

When collapsing or expanding a report, a determination is made about therange of cells to be collapsed/expanded. The spreadsheet is scanned andanalyzed to determine if an expansion or collapse operation may beperformed without disrupting the display of other data in thespreadsheet. The other data in the spreadsheet may be other reports ortext. For example, a determination is made about the existence of emptydata cells to the left and to the right of the report. If there are notany occupied data cells on either side of the report, the report may beexpanded by replacing the rows of the spreadsheet with the rows thatinclude the child members such that the child members are displayed inthe report proximate the parent member without disrupting the display ofother spreadsheet data.

However, a spreadsheet may contain so many occupied data cells that someof the occupied data cells are located proximate the spreadsheet border.Since the area of the spreadsheet is limited (e.g. 65536 rows by 256columns) report expansion may cause spreadsheet data to be pushed offthe spreadsheet. For example, a parent member may be expanded such thatnew columns/rows are added that cause data cells proximate thespreadsheet boundary to expand beyond the boundary. In order to avoiddata loss due to overexpansion, a determination is made whether thedisplay of spreadsheet data would be disrupted before a parent member isexpanded.

FIG. 5 illustrates how a determination is made to expand or collapsedata cells in a spreadsheet report without disrupting other data in thespreadsheet. Spreadsheet 500 includes collapsed report 510 (outlined inbold). Collapsed report 510 includes parent member 520. Parent member520 may include only one data cell or a group of data cells that form aportion of a row, a portion of a column, or a data region comprised ofportions of rows and columns. As shown in the figure, parent member 520includes a row of three data cells. Sibling members 530, 540 are relatedto parent member 520 and may be expanded to display associated childmembers.

A user may request the expansion of parent member 520 by double-clickingor right clicking anywhere on the parent member. Before expanding parentmember 520, spreadsheet 500 is scanned for information related to parentmember 520 to determine whether the display of other data in spreadsheet500 would be disrupted by the expansion. Area 1 includes the data cellsin spreadsheet 500 between the left border of spreadsheet 500 and theleft border of collapsed report 510. Area 2 includes the data cells inspreadsheet 500 between the right border of spreadsheet 500 and theright border of collapsed report 510. Area 3 includes the data cells inspreadsheet 500 between the bottom border of spreadsheet 500 and thebottom border of collapsed report 510. Area 4 includes the data cells inspreadsheet 500 between the top border of spreadsheet 500 and the topborder of collapsed report 510.

If none of the data cells in Areas 1 and 2 are occupied with values, thenumber of rows required for expansion may be added to the spreadsheetproximate parent member 520. Each row includes a child member associatedwith parent member 520. When the row is added to the spreadsheet, thechild members associated with parent member 520 are displayed in thereport. As shown in the lower portion of the figure, expanded report 550includes parent member 520 and child members 560, 570, 580. Siblingmembers 530, 540 are moved downward into Area 3 to make room for childmembers 560, 570, 580 which are added directly below parent member 520.In another embodiment, child members 560, 570, 580 may be located aboveparent member 520. In the case where the parent member is a column, theassociated child members may be displayed in columns to the left or theright of the parent member.

If any of the data cells in Areas 1 and 2 are occupied with values andthere is a sufficient number of empty data cells in Area 3 such that theexpansion of parent member 520 does not disrupt the display of data inAreas 3 and 4, an exact number of rows may be added within the data cellrange of the report. For example, as shown in the lower portion of thefigure, three rows may be added to create expanded report 550. The addedrows correspond to child members 560, 570, 580. Each row added to thereport is three data cells long which corresponds to the horizontal datacell range of the report. Thus, the display of data in Areas 1, 2, 3 or4 is not disrupted by the expansion.

If data cells in each of Areas 1, 2, 3, and 4 are occupied with values,then expansion may only be performed if the expansion does not disruptthe display of data in Areas 3 and 4. For example, a single data memberthree data cells in length is located in Area 3 at least four data cellsabove the lower border of the spreadsheet. The display of the datamember would not be disrupted by the expansion of collapsed report 500because the expansion would merely push the data member downward towardthe lower border of spreadsheet 500. However, if the data member waslocated within at least three data cells of the lower border ofspreadsheet 500, the expansion of collapsed report 510 would disrupt thedisplay of the data member because the data member would be pushed offspreadsheet 500.

Expanded report 550 may also be collapsed. Expanded report 550 isscanned for information associated with parent member 520 and childmembers 560, 570, 580. The information is analyzed to determine how thedisplay of related data in other areas of the spreadsheet would beaffected by the collapse. The information may include any cross-joininformation and dependencies associated with parent member 520 and childmembers 560, 570, 580. The information may also include the total numberof child members associated with parent member 520. A determination isthen made about how to collapse child members 560, 570, 580.

For example, if the data cells in Areas 1 and 2 are empty, then theentire rows where the child members are located may be removed fromdisplay in the report, and sibling members 530, 540 may be moved upwardproximate parent member 520, as shown in collapsed report 510. If thereare any occupied data cells in Areas 1 or 2 and data cells in Areas 3and 4 are empty, the rows within the report range where the childmembers are located are removed from the report such that the display ofdata in Areas 1 and 2 is not disrupted by the collapse. Sibling members530, 540 may be moved upward proximate parent member 520. If any datacells in Areas 1, 2, 3 and 4 are occupied with values, then collapse mayonly be performed if the display of data in Areas 3 and 4 would not bedisrupted.

FIG. 6 is an operational flow diagram illustrating a process forexpanding a parent member in a spreadsheet report. The process begins ata start block where a spreadsheet includes a number of occupied datacells and a report. The report includes at least one parent member. Ifthe parent member is not associated with at least one associated childmember, then expansion cannot be performed. The parent member may be asingle data cell, or a group of data cells arranged in a row, column, ora data region.

Moving to block 600, a request is received to expand a parent member.Expanding the parent member displays any associated child members in thereport in a location proximate the parent member. In one embodiment, therequest to expand is submitted by a user. For example, the user maydouble click or right-click a mouse on the parent member to submit anexpand request. The user may want to expand the parent member to displaymore detailed information associated with the parent member.

Proceeding to block 610, information related to the parent member isretrieved. In one embodiment, the information is a unique member namethat is retrieved by scanning the spreadsheet. In another embodiment,the information is retrieved from a data store associated with thespreadsheet. The information retrieved from the data store may includethe quantity of child members associated with the parent member, thenames of the child members and the dimension of the expanded reportcreated by the parent member and the associated child members.

Advancing to block 620, any dependencies on the parent member aredetermined by scanning the spreadsheet. In one embodiment, thedependencies may be cross-join information associated with the parentmember. The cross-join information allows the report to display datafrom two or more dimensions that are used in conjunction with eachother. In another embodiment, the dependencies may be other reports thatare linked to the parent member. For example, when a parent member isexpanded in a first report, an associated parent member may be expandedin a second report that is linked to the first report. All of the datacells that are dependent on the parent member are tallied to determinethe total number of data cells that would be displayed upon expansion.

Transitioning to block 630, all of the child members associated with theparent member are determined. If the parent member does not have anyassociated child members, then expansion is not possible. In oneembodiment, the number of child members associated with the parentmember is determined by batch. For example, a determination is madewhether a group of data cells are all child members. If so, adetermination is made whether a next group of data cells are childmembers. If all of the data cells in the batch are not identified aschild members that are associated with the parent member, each data cellis checked individually to determine where the list of child membersends.

Moving to block 640, a determination is made about how to expand theparent member without disrupting the display of data in other areas ofthe spreadsheet external to the report. If the areas between thevertical borders of the report and the vertical borders of thespreadsheet do not include any occupied data cells, then the rows in thespreadsheet may be replaced with rows that include the child memberssuch that the child members appear in the expanded report withoutdisrupting the display of spreadsheet data. If data cells in the areasto the left or to the right of the report are occupied with values, butdata cells in the areas above or below the report are not occupied withvalues, then the rows within the range of the report may be replacedwith the child members. If data cells in the areas above or below thereport are occupied with values, then the data cells above or below thereport may only be used to expand the report if the display of data inthe areas above or below the report is not disrupted by the expansion.If the display of data in the areas above or below the report would bedisrupted by the expansion, then the parent member cannot be expanded.

Proceeding to block 650, the expansion is performed. Any sibling membersare moved such that the child members may be displayed proximate theparent member. For example, a parent member in a collapsed report isdisplayed with a sibling member located directly below the parentmember. In order to make space for three child members associated withthe parent member, the sibling member is moved three rows down, and thethree child members are displayed in the three rows below the parentmember. The format and formula associated with the parent member isspread to the child members. In one embodiment, the format and formulamay be spread across the range of the expanded report in one direction(e.g., horizontally across a row) and then in a different direction(vertically through columns of the report). Any cross-join referencesare also accounted for such that sufficient space is made available todisplay all of the child members associated with the parent member.

FIG. 7 is an operational flow diagram illustrating a process forcollapsing a group of child members in a spreadsheet report. The processbegins at a start block where a spreadsheet includes a number ofoccupied data cells and a report. The report includes a parent memberthat has at least one associated child member. The parent cell memberand the associated child members are displayed in the report.

Moving to block 700, a request is received to collapse a report suchthat the child members are not displayed in the report. In oneembodiment, the request to collapse the report is submitted by a user.For example, the user may submit a collapse request by double clickingor right-clicking a mouse on the report to be collapsed. The user maywant to collapse the report to remove detailed information associatedwith the parent member.

Proceeding to block 710, information related to the child membersdisplayed in the report to be collapsed is obtained. The information maybe obtained by scanning the spreadsheet. The information may include thenumber of parent members and associated child members in the report, andthe dimension of the expanded report created by the parent member andthe associated child members. The dimension of the expanded reportindicates the location of data cells that are not included in theexpanded report (i.e., the position where the collapse ends).

Advancing to block 720, any dependencies on the child members aredetermined. In one embodiment, the dependencies may be cross-joininformation. The expanded report is scanned to determine whether anycross-join information exists. In another embodiment, the dependenciesmay be other reports that are related to the child members or otherwiselinked to the expanded report. For example, when a group of childmembers is collapsed in one report, an associated group of child membersmay be collapsed in a linked report. The number of data cells that aredependent on the child members are tallied to determine the spreadsheetarea affected by the collapse of the child members.

Moving to block 730, a determination is made about how to collapse thechild members of the expanded report. If the data cells are empty in thespreadsheet area to the left and to the right of the report, all of therows containing the child members are removed from the spreadsheet. Anyoccupied data cells that are located below the report may be movedupward toward the collapsed report. If the area between the left (orright) border of the report and the left (or right) border of thespreadsheet contains at least one occupied data cell, but the areabetween the lower (or upper) border of the report and the lower (orupper) border of the spreadsheet does not contain any occupied datacells, the exact number of rows that include the child members areremoved from the report range such that the display of data in otherareas of the spreadsheet external to the report is not disrupted. If thearea between the lower border of the report and the lower border of thespreadsheet contains occupied data cells, the report may only becollapsed if the display of data in other areas of the spreadsheetexternal to the report is not disrupted. The collapse of the report isthen performed at block 740 in accordance with the determined childmember collapse method. Processing then terminates at an end block.

The above specification, examples and data provide a completedescription of the manufacture and use of the composition of theinvention. Since many embodiments of the invention can be made withoutdeparting from the spirit and scope of the invention, the inventionresides in the claims hereinafter appended.

1. A computer-implemented method for expanding data cells in a report ofa spreadsheet, comprising: receiving a request to expand a parent memberof a report; scanning the spreadsheet for information related to theparent member; and expanding the parent member in the report based onthe information, wherein the parent member is expanded by displayingchild members associated with the parent member in the report withoutdisrupting the layout of data in areas of the spreadsheet external tothe report.
 2. The computer-implemented method of claim 1, wherein thereport is a free-form report that comprises page filters.
 3. Thecomputer-implemented method of claim 1, wherein the parent membercomprises a plurality of data cells.
 4. The computer-implemented methodof claim 1, wherein expanding the parent member further comprisesdisplaying child members associated with a parent member in the reportproximate the parent member.
 5. The computer-implemented method of claim1, wherein the report is a cross-joined report that assembles data froma plurality of sources.
 6. The computer-implemented method of claim 1,wherein scanning the spreadsheet for information further comprisesscanning the spreadsheet for at least one of: empty data cells betweenthe border of the report and the border of the spreadsheet, cross-joininformation associated with the report, data cells that are dependent onthe parent member, the number of child members associated with theparent member, and the dimension of the report created by the parentmember and the associated child members.
 7. The computer-implementedmethod of claim 1, wherein expanding the parent member comprises atleast one of a group comprising: replacing a row in the spreadsheet witha row containing a child member, replacing a row in the report with arow containing a child member, replacing a column in the spreadsheetwith a column containing a child member, and replacing a column in thereport with a column containing a child member.
 8. Thecomputer-implemented method of claim 1, further comprising determiningwhether a batch of child members are associated with the parent member.9. The computer-implemented method of claim 1, further comprisingcopying a format and a formula associated with the parent member to theassociated child members.
 10. A computer-implemented method forcollapsing data cells in a report of a spreadsheet, comprising:receiving a request to collapse child cells associated with a parentcell of the report; scanning the spreadsheet for information related tothe parent cell and the associated child cells; and collapsing the childcells in the report based on the information, wherein the child membersare collapsed by removing the child members associated with the parentmember from being displayed in the report without disrupting the layoutof data in areas of the spreadsheet external to the report.
 11. Thecomputer-implemented method of claim 10, wherein collapsing the childcells further comprises at least one of: removing a row containing achild cell from the spreadsheet, removing a row containing a child cellfrom the report, moving a row containing a sibling cell to be proximatea row containing the parent cell, removing a column containing a childcell from the spreadsheet, removing a column containing a child cellfrom the report, and moving a column containing a sibling cell to beproximate a column containing the parent cell.
 12. Thecomputer-implemented method of claim 10, wherein the informationcomprises at least one of: the total number of data cells in the report,the dimension of the report, the data cells in the report that aredependent on other data cells external to the report within thespreadsheet, cross-join information associated with the report, andother related reports within the spreadsheet.
 13. A computer-readablemedium having computer-executable instructions for expanding data cellsin a report of a spreadsheet, comprising: receiving a request to expanda parent cell in the report; retrieving information related to theparent cell from the spreadsheet; and expanding the parent cell in thereport based on the information, wherein the parent cell is expanded bydisplaying a child cell associated with the parent cell in the reportwithout disrupting the display of other occupied data cells in thespreadsheet external to the report.
 14. The computer-readable medium ofclaim 13, wherein expanding the parent cell further comprises displayingthe child cell in the report proximate the parent cell.
 15. Thecomputer-readable medium of claim 13, wherein retrieving informationrelated to the parent cell further comprises scanning the spreadsheetfor at least one of: empty data cells between the border of the reportand the border of the spreadsheet, cross-join information associatedwith the report, data cells that are dependent on the parent cell, thenumber of child cells associated with the parent cell, and the dimensionof the report created by the parent cell and the associated child cells.16. The computer-readable medium of claim 13, wherein expanding theparent cell comprises at least one of: replacing a row in thespreadsheet with a row containing the child cell, replacing a row in thereport with a row containing the child cell, replacing a column in thespreadsheet with a column containing the child cell, and replacing acolumn in the report with a column containing the child cell.
 17. Acomputer-readable medium having computer-executable instructions forcollapsing child members associated with a parent member in a report ofa spreadsheet, comprising: receiving a request to collapse the childmembers; retrieving information related to the parent member and thechild members from the spreadsheet; and collapsing the child members inthe report based on the information, wherein the child members arecollapsed by removing the child members from the report withoutdisrupting the display of occupied data cells in the spreadsheetexternal to the report.
 18. The computer-readable medium of claim 17,wherein collapsing the child members further comprises at least one of:removing rows containing the child members from the spreadsheet,removing rows containing the child members from the report, moving a rowcontaining a sibling member to be proximate a row containing the parentmember, removing columns containing the child members from thespreadsheet, removing columns containing the child members from thereport, and moving a column containing a sibling member to be proximatea column containing the parent member.
 19. The computer-readable mediumof claim 17, wherein the information comprises at least one of: thetotal number of data cells in the report, the dimension of the report,the data cells in the report that are dependent on other data cellsexternal to the report within the spreadsheet, cross-join informationassociated with the report, and other related reports within thespreadsheet.